﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SapLop.Objects;
using System.ComponentModel;

namespace SapLop.Data
{
    public class DataAccess
    {
        public static SqlConnection KetNoiCSDL(string strChuoiKetNoi = @"Data Source=THANHTUAN-PC;Initial Catalog=CT30;Integrated Security=true;")
        {
            SqlConnection conn = new SqlConnection(strChuoiKetNoi);
            try
            {
                conn.Open();
            }
            catch (SqlException ex)
            {
                throw new Exception("Lỗi kết nối CSDL");
            }
            return conn;
        }
        
        // Lấy DataTable
        public DataTable LayTable(string strTruyVan, SqlConnection conn)
        {
            SqlDataAdapter da = new SqlDataAdapter(strTruyVan, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public List<SinhVienOBJ> DanhSachSVChuaSapLop(string strMaNganh = "")
        {
            List<SinhVienOBJ> dsSV = new List<SinhVienOBJ>();
            SqlConnection conn = KetNoiCSDL();
            string strTruyVan = "SELECT dk.MASV, s.HO, s.TEN, l.LOP, s.NU, dk.MANGANH FROM DKCHUYENNGANH dk"
                + " INNER JOIN SINHVIEN s ON s.MASV = dk.MASV"
                + " INNER JOIN LOP l on l.MALOP = s.MALOP";
            if (strMaNganh != "")
                strTruyVan += " WHERE dk.MANGANH = '" + strMaNganh + "'";
            SqlCommand com = new SqlCommand(strTruyVan, conn);

            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                SinhVienOBJ sv = new SinhVienOBJ();
                sv.MSSV = (string)dr["MASV"];
                sv.Ho = (string)dr["HO"];
                sv.Ten = (string)dr["TEN"];
                sv.Lop = (string)dr["LOP"];
                sv.Phai = (bool)dr["NU"]?"Nữ":"Nam";
                sv.MaChuyenNganhDK = (string)dr["MANGANH"];
                dsSV.Add(sv);
            }
            dr.Close();
            conn.Close();
            return dsSV;
        }

        public BindingList<SinhVienOBJ> DanhSachSVCuaLop(string strMaLop = "")
        {
            BindingList<SinhVienOBJ> dsSV = new BindingList<SinhVienOBJ>();
            SqlConnection conn = KetNoiCSDL();
            string strTruyVan = "SELECT * FROM SINHVIEN";
            if (strMaLop != "")
                strTruyVan += " WHERE MALOP = '" + strMaLop + "'";
            SqlCommand com = new SqlCommand(strTruyVan, conn);

            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                SinhVienOBJ sv = new SinhVienOBJ();
                sv.MSSV = (string)dr["MASV"];
                sv.Ho = (string)dr["HO"];
                sv.Ten = (string)dr["TEN"];
                //sv.Lop = (string)dr["LOP"];
                sv.MaLopMoi = (string)dr["MALOP"];
                sv.Phai = (bool)dr["NU"] ? "Nữ" : "Nam";
                //sv.MaChuyenNganhDK = (string)dr["MANGANH"];
                dsSV.Add(sv);
            }
            dr.Close();
            conn.Close();
            return dsSV;
        }

        public void LuuSinhVienLop(string strMaSV, string strMaLop)
        {
            SqlConnection conn = KetNoiCSDL();
            string strTruyVan = "INSERT INTO SINHVIEN_LOP VALUES(@masv, @malop, (SELECT GETDATE()), '');";
            SqlCommand com = new SqlCommand(strTruyVan, conn);
            com.Parameters.AddWithValue("@masv", strMaSV);
            com.Parameters.AddWithValue("@malop", strMaLop);
            com.ExecuteNonQuery();
            conn.Close();
        }

        public void LuuSinhVien(string strMaSV, string strMaLop)
        {
            SqlConnection conn = KetNoiCSDL();
            string strTruyVan = "UPDATE SINHVIEN SET MALOP = @malop WHERE MASV = @masv";
            SqlCommand com = new SqlCommand(strTruyVan, conn);
            com.Parameters.AddWithValue("@masv", strMaSV);
            com.Parameters.AddWithValue("@malop", strMaLop);
            com.ExecuteNonQuery();
            conn.Close();
        }

        public void CapNhatXepLop(string strMaSV)
        {
            SqlConnection conn = KetNoiCSDL();
            string strTruyVan = "UPDATE DKCHUYENNGANH SET DAXEPLOP = 'true' WHERE MASV = @masv";
            SqlCommand com = new SqlCommand(strTruyVan, conn);
            com.Parameters.AddWithValue("@masv", strMaSV);
            com.ExecuteNonQuery();
            conn.Close();
        }
    }
}
